/**
 * 
 */
package com.feib.stms.dao.jdbc;


import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.UserDao;
import com.feib.stms.dao.jdbc.rowmapper.UserRowMapper;
import com.feib.stms.model.Group;
import com.feib.stms.model.User;
import com.feib.stms.pagination.PaginatedList;
import com.feib.stms.util.UserUtil;

/**
 * @author Jimmy Liu
 *
 */
@Repository("userDao")
public class UserJdbcDao extends BaseJdbcDao implements UserDao 
{
	private OracleSequenceMaxValueIncrementer sequence;

	private SimpleJdbcInsert userJdbcInsert;

	@Override
	protected void initDao() throws Exception {
		super.initDao();
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "APP_USER_PK_SEQ");

		this.userJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.userJdbcInsert.withTableName("APP_USER")
			.usingColumns("USER_PK", "GROUP_PK","SECURITIES_PK", "USER_ID", "USER_PWD", "USER_NAME", "USER_EMAIL"
					, "USER_PHONE_NO", "DEAL_SECURITIES_NOS", "IS_LOCKED", "IS_ENABLED", "USER_NOTE"
					, "CREATED_DATE", "CREATED_BY");

	}

	public User getByUserIdGroupNo(User user) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();

		sql.append("SELECT U.*");
		sql.append(" , G.GROUP_NAME, G.GROUP_NO, G.GROUP_TYPE, G.GROUP_NOTE, G.CONTACT_NAME, G.CONTACT_EMAIL, G.CONTACT_TELNO, G.CONTACT_CELLNO, G.GROUP_ADDRESS, G.UBM");
		sql.append(" ,S.SECURITIES_NO,S.SECURITIES_NAME ");
		sql.append(" FROM APP_USER U");
		sql.append(" LEFT JOIN APP_GROUP G ON G.GROUP_PK = U.GROUP_PK");
		sql.append(" LEFT JOIN SECURITIES S ON S.SECURITIES_PK = U.SECURITIES_PK");
		sql.append(" WHERE U.USER_ID = :USER_ID AND G.GROUP_NO = :GROUP_NO");
		parameters.addValue("USER_ID",  user.getUserId(), Types.VARCHAR);
		parameters.addValue("GROUP_NO",  null == user.getGroup() ? null: user.getGroup().getGroupNo(), Types.VARCHAR);

		return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new UserRowMapper(), parameters));
	}

	public int doLoginSuccess(User user) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("UPDATE APP_USER SET ");
		sql.append(" IS_LOCKED = :IS_LOCKED");
		sql.append(" ,IS_ENABLED = :IS_ENABLED");
		sql.append(" ,LAST_LOGIN_DATE = :LAST_LOGIN_DATE");
		sql.append(" ,PWD_ERROR_TIME = :PWD_ERROR_TIME");
		sql.append(" ,CAPTCHA_ERROR_TIME = :CAPTCHA_ERROR_TIME");
		sql.append(" ,UPDATED_DATE = :UPDATED_DATE");
		sql.append(" ,UPDATED_BY = :UPDATED_BY");
		sql.append(" WHERE USER_PK = :USER_PK");

		parameters.addValue("IS_LOCKED",  convertToInteger(user.getLocked()) , Types.NUMERIC);
		parameters.addValue("IS_ENABLED",  convertToInteger(user.getEnabled()) , Types.NUMERIC);
		parameters.addValue("LAST_LOGIN_DATE",  user.getLastLoginDate(), Types.TIMESTAMP);
		parameters.addValue("PWD_ERROR_TIME",  user.getPwdErrorTime(), Types.NUMERIC);
		parameters.addValue("CAPTCHA_ERROR_TIME",  user.getCaptchaErrorTime(), Types.NUMERIC);


		parameters.addValue("UPDATED_DATE",  user.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("UPDATED_BY",  user.getUpdatedBy(), Types.VARCHAR);

		parameters.addValue("USER_PK",  user.getUserPk(), Types.NUMERIC);

		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	public int doLoginFail(User user) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("UPDATE APP_USER SET ");
		sql.append(" PWD_ERROR_TIME = :PWD_ERROR_TIME");
		sql.append(" ,CAPTCHA_ERROR_TIME = :CAPTCHA_ERROR_TIME");
		sql.append(" ,UPDATED_DATE = :UPDATED_DATE");
		sql.append(" ,UPDATED_BY = :UPDATED_BY");
		sql.append(" WHERE USER_PK = :USER_PK");

		parameters.addValue("PWD_ERROR_TIME", user.getPwdErrorTime(), Types.NUMERIC);
		parameters.addValue("CAPTCHA_ERROR_TIME",  user.getCaptchaErrorTime(), Types.NUMERIC);


		parameters.addValue("UPDATED_DATE",  user.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("UPDATED_BY",  user.getUpdatedBy(), Types.VARCHAR);

		parameters.addValue("USER_PK",  user.getUserPk(), Types.NUMERIC);

		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	public User getByUserPk(Long userPk)
	{
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT U.*");
		sql.append(" , G.GROUP_NAME, G.GROUP_NO, G.GROUP_TYPE, G.GROUP_NOTE, G.CONTACT_NAME, G.CONTACT_EMAIL, G.CONTACT_TELNO, G.CONTACT_CELLNO, G.GROUP_ADDRESS, G.UBM");
		sql.append(" FROM APP_USER U, APP_GROUP G");
		sql.append(" WHERE G.GROUP_PK = U.GROUP_PK AND U.USER_PK = :USER_PK");
		parameters.addValue("USER_PK", userPk, Types.NUMERIC);

		return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new UserRowMapper(), parameters));
	}

	public int doChangePwdSuccess(User user) 
	{
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("UPDATE APP_USER SET ");
		sql.append(" PWD_ERROR_TIME = :PWD_ERROR_TIME");
		sql.append(" , CAPTCHA_ERROR_TIME = :CAPTCHA_ERROR_TIME");
		sql.append(" , USER_PWD = :USER_PWD");
		sql.append(" , PWD_HISTORY = :PWD_HISTORY");
		sql.append(" , PWD_EXPIRED_DATE = :PWD_EXPIRED_DATE");
		sql.append(" , UPDATED_DATE = :UPDATED_DATE");
		sql.append(" , UPDATED_BY = :UPDATED_BY");
		sql.append(" WHERE USER_PK = :USER_PK");

		parameters.addValue("PWD_ERROR_TIME",  user.getPwdErrorTime(), Types.NUMERIC);
		parameters.addValue("CAPTCHA_ERROR_TIME",  user.getCaptchaErrorTime(), Types.NUMERIC);

		parameters.addValue("USER_PWD",  user.getUserPwd(), Types.VARCHAR);
		parameters.addValue("PWD_HISTORY",  user.getPwdHistory(), Types.VARCHAR);
		parameters.addValue("PWD_EXPIRED_DATE",  user.getPwdExpiredDate(), Types.TIMESTAMP);

		parameters.addValue("UPDATED_DATE",  user.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("UPDATED_BY",  user.getUpdatedBy(), Types.VARCHAR);

		parameters.addValue("USER_PK",  user.getUserPk(), Types.NUMERIC);

		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.UserDao#insert(com.feib.stms.model.User)
	 */
	public int insert(User user) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		Long userPk = new Long(this.sequence.nextLongValue());
		parameters.addValue("USER_PK",  userPk, Types.NUMERIC);
		parameters.addValue("GROUP_PK",  user.getGroup().getGroupPk(), Types.NUMERIC);
		parameters.addValue("SECURITIES_PK",  user.getSecurities()==null?user.getSecurities():user.getSecurities().getSecuritiesPk(), Types.NUMERIC);
		parameters.addValue("USER_ID", user.getUserId(), Types.VARCHAR);
		parameters.addValue("USER_PWD", user.getUserPwd(), Types.VARCHAR);
		parameters.addValue("USER_NAME",  user.getUserName(), Types.VARCHAR);
		parameters.addValue("USER_EMAIL",  user.getUserEmail(), Types.VARCHAR);
		parameters.addValue("USER_PHONE_NO",  user.getUserPhoneNo(), Types.VARCHAR);
		parameters.addValue("DEAL_SECURITIES_NOS",  user.getDealSecuritiesNos(), Types.VARCHAR);
		parameters.addValue("IS_LOCKED",  convertToInteger(user.getLocked()), Types.NUMERIC);
		parameters.addValue("IS_ENABLED",  convertToInteger(user.getEnabled()), Types.NUMERIC);
		parameters.addValue("USER_NOTE",  user.getUserNote(), Types.VARCHAR);		
		parameters.addValue("CREATED_BY",  user.getCreatedBy(), Types.VARCHAR);
		parameters.addValue("CREATED_DATE",  user.getCreatedDate(), Types.TIMESTAMP);
		
		
		int insertCounts = userJdbcInsert.execute(parameters);
		if (insertCounts > 0)
			user.setUserPk(userPk);
		return insertCounts;
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.UserDao#queryForPaginatedListByUser(com.feib.stms.model.User, com.feib.stms.pagination.PaginatedList)
	 */
	public PaginatedList<User> queryForPaginatedListByUser(User user, PaginatedList paginatedList) {
		SqlQueryProvider sqlQueryProvider = getSqlQueryProviderByGroup(user, paginatedList);
		return this.queryForPaginatedList(sqlQueryProvider, new UserRowMapper(), paginatedList.getPageNumber(), paginatedList.getPageSize());
	}

	/*
	 * 拼接分頁查詢sql
	 */
	protected SqlQueryProvider getSqlQueryProviderByGroup(User user , PaginatedList paginatedList)
	{
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();

		/* 查詢條件 */
		List<Object> args = new ArrayList<Object>();

		sql.append("SELECT U.* ,L.IS_ACTIVED");
		sql.append(" FROM APP_USER U LEFT JOIN USER_LOGIN_CONTROL L ON U.USER_PK = L.USER_PK WHERE 1 = 1 ");
		if (null != user)
		{	
			/* 使用者ID */
			if(!StringUtils.isEmpty(user.getUserId())){
				sql.append(" AND U.USER_ID = ?");
				args.add(user.getUserId());
			}
			/* 姓名 */
			if(!StringUtils.isEmpty(user.getUserName())){
				sql.append(" AND U.USER_NAME LIKE ?");
				args.add("%" + user.getUserName() + "%");
			}			
		}
		//非銀行端，則不能全查，只能查詢自己所屬Group
		if(!UserUtil.isBankGroup()){
			sql.append(" AND U.GROUP_PK = ?");
			args.add(UserUtil.getUserGroup().getGroupPk());
		}
		/* 排序 */
		if (null != paginatedList)
		{
			if (StringUtils.isNotEmpty(paginatedList.getSortCriterion()))
			{
				sql.append(" ORDER BY ").append(paginatedList.getSortCriterion()).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}
		SqlQueryProvider sqlQueryProvider = new SqlQueryProvider(sql.toString(), args);
		return sqlQueryProvider;
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.UserDao#countUserId(java.lang.String, java.lang.Long)
	 */
	public int countUserId(String userId,Long groupPk) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT  COUNT(*)  FROM APP_USER WHERE USER_ID = :USER_ID");
		sql.append(" AND GROUP_PK = :GROUP_PK");
		parameters.addValue("USER_ID",  userId, Types.VARCHAR);
		parameters.addValue("GROUP_PK", groupPk, Types.NUMERIC);
		int groupCount = this.getSimpleJdbcTemplate().queryForInt(sql.toString(), parameters);
		return groupCount;
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.UserDao#countUserId(java.lang.Long, java.lang.String, java.lang.Long)
	 */
	public int countUserId(Long userPk, String userId,Long groupPk) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT  COUNT(*)  FROM APP_USER WHERE USER_ID = :USER_ID AND USER_PK != :USER_PK");
		sql.append(" AND GROUP_PK = :GROUP_PK");
		parameters.addValue("USER_ID",  userId, Types.VARCHAR);
		parameters.addValue("USER_PK",  userPk, Types.NUMERIC);
		parameters.addValue("GROUP_PK",  groupPk, Types.NUMERIC);
		int groupCount = this.getSimpleJdbcTemplate().queryForInt(sql.toString(), parameters);
		return groupCount;
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.UserDao#update(com.feib.stms.model.User)
	 */
	public int update(User user) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("UPDATE APP_USER SET ");
		sql.append(" USER_NAME = :USER_NAME");
		sql.append(" , USER_PHONE_NO = :USER_PHONE_NO");
		sql.append(" , USER_EMAIL = :USER_EMAIL");
		sql.append(" , IS_ENABLED = :IS_ENABLED");
		sql.append(" , IS_LOCKED = :IS_LOCKED");
		sql.append(" , CERTIFICATE_KEY = :CERTIFICATE_KEY");
		sql.append(" , USER_NOTE = :USER_NOTE");
		sql.append(" , DEAL_SECURITIES_NOS = :DEAL_SECURITIES_NOS");
		sql.append(" , SECURITIES_PK = :SECURITIES_PK");
		sql.append(" , UPDATED_BY = :UPDATED_BY");
		sql.append(" , UPDATED_DATE = :UPDATED_DATE");
		sql.append(" WHERE USER_ID = :USER_ID");
		sql.append(" AND GROUP_PK = :GROUP_PK");

		parameters.addValue("USER_NAME",  user.getUserName(), Types.VARCHAR);
		parameters.addValue("USER_PHONE_NO",  user.getUserPhoneNo(), Types.VARCHAR);
		parameters.addValue("USER_EMAIL",  user.getUserEmail(), Types.VARCHAR);
		parameters.addValue("IS_ENABLED",  convertToInteger(user.getEnabled()), Types.NUMERIC);
		parameters.addValue("IS_LOCKED",  convertToInteger(user.getLocked()), Types.NUMERIC);
		parameters.addValue("CERTIFICATE_KEY",  user.getCertificateKey(), Types.VARCHAR);
		parameters.addValue("USER_NOTE",  user.getUserNote(), Types.VARCHAR);
		parameters.addValue("DEAL_SECURITIES_NOS",  user.getDealSecuritiesNos(), Types.VARCHAR);
		parameters.addValue("SECURITIES_PK",  user.getSecurities().getSecuritiesPk(), Types.NUMERIC);
		parameters.addValue("UPDATED_BY", user.getUpdatedBy(), Types.VARCHAR);
		parameters.addValue("UPDATED_DATE",  user.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("USER_ID",  user.getUserId(), Types.VARCHAR);
		parameters.addValue("GROUP_PK",  user.getGroup().getGroupPk(), Types.NUMERIC);

		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.UserDao#findByUserId(java.lang.String, java.lang.Long)
	 */
	public User findByUserId(String userId,Long groupPk) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT U.* ,L.IS_ACTIVED");
		sql.append(" FROM APP_USER U LEFT JOIN USER_LOGIN_CONTROL L ON U.USER_PK = L.USER_PK");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND U.USER_ID = :USER_ID");
		sql.append(" AND U.GROUP_PK = :GROUP_PK");
		parameters.addValue("USER_ID",  userId, Types.VARCHAR);
		parameters.addValue("GROUP_PK",  groupPk, Types.NUMERIC);

		return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new UserRowMapper(), parameters));
	}

	public List<String> findUserEmailByGroupMenuId(Group group, Long menuItemPk, Long uploadFilePk) {

		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		List<Object> args = new ArrayList<Object>();

		sql.append(" SELECT U.USER_EMAIL ");
		sql.append(" FROM APP_USER U " );
		sql.append(" INNER JOIN APP_USER_ROLE R ");
		sql.append(" ON U.GROUP_PK = ? AND  U.USER_PK = R.USER_PK");
		sql.append(" INNER JOIN  APP_ROLE_MENU  M ");
		sql.append(" ON  M.MENU_ITEM_PK= ?  AND M.ROLE_PK=R.ROLE_PK");

		args.add(group.getGroupPk());
		args.add(menuItemPk);

		if(null != uploadFilePk){
			sql.append(" WHERE NOT EXISTS (");
			sql.append(" SELECT 'X' FROM  UPLOAD_FILE_ACTION_LOG L   ");
			sql.append(" WHERE L.UPLOAD_FILE_PK = ?  ");	
			sql.append(" AND L.USER_PK = U.USER_PK  )");
			args.add(uploadFilePk);
		}

		List<Map<String, Object>> rds = this.getJdbcTemplate().queryForList(sql.toString(),args.toArray());
		
		logger.debug("SQL: {}", sql.toString());
		logger.debug("Args: {}", args.toString());

		
		List<String> result = new ArrayList<String>();		
		for(Map<String, Object> rd : rds)
			result.add((String)rd.get("USER_EMAIL"));

		rds.clear();

		return result;
	}


}
